﻿'Description:零件质量追溯
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:2018-12-11
'Rewriter:
'Rewrite Date:
Imports BusinessRule.DTO
Imports BusinessRule.IBusiness
Imports System.Text
Imports DatabaseAccess.DBUtility
Imports System.Data.OracleClient
Imports BusinessRule.Common

Public Class uLMSProductQualityTraceBusiness
    Implements uLMSProductQualityTraceIBusiness
    ''' <summary>
    ''' 获取零件追溯数据
    ''' add:Wangjh 20181211
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetProductQualityTraceData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSProductQualityTraceIBusiness.GetProductQualityTraceData
        Dim resultObj As New uMESProcessResult
        '验证输入的批次
        Dim conSql As String = <string>SELECT ut.attributevalue checkNUm,cci.*  FROM containercurrentinfo cci
                            LEFT JOIN Userattribute ut ON ut.parentid=cci.containerid AND ut.userattributename='检验编号'
                            WHERE cci.containerstatus!=0 </string>
        conSql += " AND cci.containername='" + para("ContainerName") + "'"
        Dim conDt As DataTable = DatabaseHelper.Query_ReturnDatTable(conSql)
        If conDt.Rows.Count = 0 Then
            resultObj.Message = "输入的批次不存在"
            Return resultObj
        End If
        If conDt.Rows(0)("originalfactoryid") <> para("FactoryID") Then
            resultObj.Message = "输入的批次不是当前分厂开卡"
            Return resultObj
        End If
        '
        Dim strSql As String = ""
        strSql = <string>SELECT *  FROM (
                SELECT cci.containername FA,cm.productname||'/'||cm.productbatchno SON FROM issueerpmaterial em
                LEFT JOIN Collecterpmaterial cm ON cm.collecterpmaterialid=em.collecterpmaterialid
                LEFT JOIN containercurrentinfo cci ON cci.containerid=em.containerid
                GROUP BY cci.containername,cm.productname,cm.productbatchno
                ) T</string>
        If para("Status") = "1" Then '正向 向上找
            strSql += " START WITH T.SON='" + para("ContainerName") + "'"
            strSql += " CONNECT BY PRIOR T.FA= T.SON"
        ElseIf para("Status") = "2" Then '逆向 向下找，只找一层
            strSql += " where T.FA='" + para("ContainerName") + "'"
        End If

        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        Dim result As New DataTable
        Dim num As Integer = 0
        Dim index As Integer = 0
        result.Columns.Add("CheckNum")
        result.Columns("CheckNum").DefaultValue = conDt.Rows(0)("checkNUm")
        result.Columns.Add("OriContainerName")
        result.Columns("OriContainerName").DefaultValue = TransferLink(para("ContainerName"))
        result.Columns.Add("Qty")
        result.Columns("qty").DefaultValue = conDt.Rows(0)("qty")
        result.Columns.Add("index")
        If para("Status") = "1" Then '正向 向上找
            TransferTable(dt, para("ContainerName"), num, result, index)
        ElseIf para("Status") = "2" Then '逆向 向下找
            Dim row As DataRow = result.NewRow
            For i As Integer = 0 To dt.Rows.Count - 1
                Dim faColName As String = "SON" + (i + 1).ToString
                result.Columns.Add(faColName)
                row(faColName) = TransferLink(dt.Rows(i)("SON").ToString)
            Next
            result.Rows.Add(row)
        End If
        result.DefaultView.Sort = "index asc"
        result = result.DefaultView.ToTable
        result.AcceptChanges()
        result.TableName = "result"

        Dim resultData As DataTable = result.Copy
        resultData.Columns.Remove("CheckNum")
        resultData.Columns.Remove("OriContainerName")
        resultData.Columns.Remove("Qty")
        resultData.TableName = "resultData"
        resultData.AcceptChanges()

        Dim ds As New DataSet
        ds.Tables.Add(result)
        ds.Tables.Add(resultData)
        resultObj.DBDataSet = ds
        resultObj.DBTable = result
        resultObj.PageCount = Math.Ceiling(result.Rows.Count / Convert.ToInt32(para("PageSize").ToString))
        resultObj.RowCount = Convert.ToInt32(para("PageSize").ToString)
        GetProductQualityTraceData = resultObj
    End Function

    ''' <summary>
    ''' datatable处理
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="son"></param>
    ''' <param name="num"></param>
    ''' <param name="result"></param>
    ''' <remarks></remarks>
    Sub TransferTable(ByVal dt As DataTable, ByVal son As String, ByRef num As Integer, ByRef result As DataTable, ByVal rowIndex As Integer)
        '第一次
        Dim temView As DataView = dt.DefaultView
        Dim tempDt As New DataTable
        temView.RowFilter = "SON='" + son + "'"
        tempDt = temView.ToTable
        If tempDt.Rows.Count > 0 Then
            '放入到结果表
            num += 1
            Dim faColName As String = "FA" + num.ToString
            result.Columns.Add(faColName)

            For Each dr As DataRow In tempDt.Rows
                rowIndex += 1
                If num = 1 Then
                    Dim row As DataRow = result.NewRow
                    row(faColName) = TransferLink(dr("FA").ToString)
                    row("index") = rowIndex
                    result.Rows.Add(row)
                Else
                    Dim tempDrs As DataRow() = result.Select("FA" + (num - 1).ToString + "='" + TransferLink(dr("SON").ToString) + "'")
                    If tempDrs.Length > 0 Then
                        If tempDrs(0).IsNull("FA" + num.ToString) Then
                            tempDrs(0)("FA" + num.ToString) = TransferLink(dr("FA").ToString)
                            rowIndex -= 1
                        Else
                            Dim newDr As DataRow = result.NewRow  'tempDrs(0)
                            newDr.ItemArray = tempDrs(0).ItemArray.Clone
                            newDr("FA" + num.ToString) = TransferLink(dr("FA").ToString)
                            newDr("index") = tempDrs(0)("index")
                            result.Rows.Add(newDr)
                        End If
                    End If


                    'Dim tempResultDt As DataTable = result.Clone
                    'For Each _dr As DataRow In result.Rows
                    '    If _dr("FA" + (num - 1).ToString) = dr("SON").ToString Then
                    '        If _dr.IsNull("FA" + num.ToString) Then
                    '            _dr("FA" + num.ToString) = dr("FA")
                    '        Else
                    '            Dim newDr As DataRow = _dr
                    '            newDr("FA" + num.ToString) = dr("FA")
                    '            tempResultDt.ImportRow(newDr)
                    '        End If

                    '    End If
                    'Next
                    'result.Merge(tempResultDt)
                End If
            Next
            '

            For Each dr As DataRow In tempDt.Rows
                TransferTable(dt, dr("FA").ToString, num, result, rowIndex)
            Next
        End If
    End Sub

    ''' <summary>
    ''' 转化链接
    ''' </summary>
    ''' <param name="value"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function TransferLink(ByVal value As String) As String
        Return "<a href=""#"" class=""astyle"" onclick=openSpecDetailwinsNew(""" & value & """)>" & value & "</a>"
    End Function

    ''' <summary>
    ''' 获取批次详细质量信息
    ''' </summary>
    ''' <param name="containerName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerQualityTraceDetails(ByVal containerName As String) As uMESProcessResult Implements uLMSProductQualityTraceIBusiness.GetContainerQualityTraceDetails
        Dim strSql As String = ""
        Dim dt As New DataTable
        Dim resultObj As New uMESProcessResult
        '验证批次是否存在
        strSql = " select * from containercurrentinfo cci where cci.containerstatus<>0 and cci.containername='" + containerName + "'"
        Dim conDt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        If conDt.Rows.Count = 0 Then
            resultObj.Message = "批次不存在(可能未创建或被删除)"
            Return resultObj
        End If
        '
        '不合格审理
        strSql = <string>SELECT e.fullname employeeName,e2.fullname checkName ,cno.*  FROM CONTAINERNONCONFORMINGINFO cno
                LEFT JOIN employee e ON e.employeeid=cno.employeeid LEFT JOIN employee e2 ON e2.employeeid=cno.checkemployeeid</string>
        strSql += String.Format(" where cno.isuse=0 and cno.zpch2='{0}' order by cno.Workflowstepid asc", containerName)
        dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        dt.TableName = "ContainerNonconformingInfo"
        resultObj.DBDataSet.Tables.Add(dt)
        '
        '废品单信息
        strSql = <string>SELECT e.fullname LossEmployeeName,e2.fullname CheckName,ws.workflowstepname,csi.* FROM containerstepscrapinfo csi
                    LEFT JOIN workflowstep ws ON ws.workflowstepid=csi.workflowstepid
                    LEFT JOIN employee e ON e.employeeid=csi.employeeid
                    LEFT JOIN employee e2 ON e2.employeeid=csi.checkemployeeid
                    WHERE csi.isuse=0</string>
        strSql += String.Format(" and csi.containerid='{0}' order by ws.Workflowstepid asc", conDt.Rows(0)("containerid").ToString)
        dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        dt.TableName = "ContainerStepScrapInfo"
        resultObj.DBDataSet.Tables.Add(dt)
        '
        '成品检验信息
        strSql = <string>
                   SELECT ws.workflowstepname,e.fullname CheckerName,cfci.*  FROM containerfinishedcheckinfo cfci
                    LEFT JOIN workflowstep ws ON ws.workflowstepid=cfci.workflowstepid
                    LEFT JOIN employee e ON e.employeeid=cfci.checkerid
                    WHERE cfci.isuse=0
               </string>
        strSql += String.Format(" AND cfci.containerid='{0}' order by ws.Workflowstepid asc", conDt.Rows(0)("containerid").ToString)
        dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        dt.TableName = "ContainerFinishedCheckInfo"
        resultObj.DBDataSet.Tables.Add(dt)
        '
        '返工返修信息
        strSql = <string>  
                    SELECT nvl(ls.lossreasonname,swd.lossreasonid) lossreasonname,ws.workflowstepname,swd.qty,sw.*  FROM stepreworkinfo sw
                    LEFT JOIN stepreworkinfodetail swd ON swd.stepreworkinfoid=sw.stepreworkinfoid
                    LEFT JOIN lossreason ls ON ls.lossreasonid=swd.lossreasonid
					LEFT JOIN workflowstep ws ON ws.workflowstepid=sw.workflowstepid
               </string>
        strSql += String.Format(" where  sw.containerid='{0}' order by ws.Workflowstepid asc", conDt.Rows(0)("containerid").ToString)
        dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        dt.TableName = "StepReworkInfo"
        resultObj.DBDataSet.Tables.Add(dt)
        '

        '委托计量信息
        strSql = <string>  
                    SELECT DECODE(en.ischeckdetection,0,'否','是') IsCheck,ws.workflowstepname,e.fullname Entrustor,e1.fullname TreatEmployee,ed.checkpointname,ed.checkpointnote,ed.checkpointresultnote,ed.drawingno,en.*  FROM Entrustmeasurement en
                    LEFT JOIN workflowstep ws ON ws.workflowstepid=en.workflowstepid
                    LEFT JOIN employee e ON e.employeeid=en.Entrustorid
                    LEFT JOIN employee e1 ON e1.employeeid=en.TreatEmployeeID
                    LEFT JOIN Entrustmeasurementdetail ed ON ed.entrustmeasurementid=en.entrustmeasurementid
                </string>
        strSql += String.Format(" where  en.containerid='{0}' order by ws.Workflowstepid asc", conDt.Rows(0)("containerid").ToString)
        dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        dt.TableName = "EntrustMeasurement"
        resultObj.DBDataSet.Tables.Add(dt)
        '



        Return resultObj
    End Function
End Class
